//Documentation complete
/**
 * @author Jason Funk
 * @version 2009MR05
 */

package broker;

import static problemDomain.ErrorBean.throwError;
import static problemDomain.ErrorBean.ErrorCode.BROKER_DELETE_ERROR;
import static problemDomain.ErrorBean.ErrorCode.BROKER_INSERT_ERROR;
import static problemDomain.ErrorBean.ErrorCode.BROKER_SELECT_ERROR;
import static problemDomain.ErrorBean.ErrorCode.BROKER_UPDATE_ERROR;
import problemDomain.*;

import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * <code>Broker</code> for <code>PaymentBean</code>.
 */
public class PaymentBroker extends Broker<PaymentBean> {
	
	/* Singleton Constructor */
	
	private static PaymentBroker broker;
	
	private PaymentBroker () throws ErrorBean {
		insertSQL =
		updateSQL =
		deletePaymentSQL = null;
	}
	
	/**
	 * Retrieves the static DeliveryBroker instance.
	 * @return static DeliveryBroker
	 * @throws ErrorBean
	 */
	public synchronized static PaymentBroker getBroker () throws ErrorBean {
		if (broker == null)
			broker = new PaymentBroker();
		return broker;
	}
	
	/* END Singleton Constructor */
		
	/* Prepared Statements */
	
	private CallableStatement insertSQL;
	private CallableStatement getInsertSQL () throws ErrorBean {
		return prepare(insertSQL, "CALL sp_create_payment(?, ?, ?, ?);");
	}
	
	private CallableStatement updateSQL;
	private CallableStatement getUpdateSQL () throws ErrorBean {
		return prepare(updateSQL, "CALL sp_update_payment(?, ?, ?, ?, ?);");
	}
	
	private CallableStatement deletePaymentSQL;
	private CallableStatement getDeletePaymentSQL () throws ErrorBean {
		return prepare(deletePaymentSQL, "CALL sp_delete_payment(?)");
	}

	/* END Prepared Statements */
	
	
	/* (Broker) Methods */
	
	@Override
	public List<PaymentBean> listAll () throws ErrorBean {
		List<PaymentBean> search = new ArrayList<PaymentBean>();
		PaymentBean pb;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM payment;");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
			
				while (results.next()) {
					pb = new PaymentBean();
					pb.setId(results.getInt("payment_id"));
					pb.setPaymentMethod(results.getString("payment_method"));
					pb.setAmount(results.getDouble("amount"));
					
					//Retrieve the order
					int order_id = results.getInt("fk_order_id");
					OrderBroker ob = OrderBroker.getBroker();
					OrderBean searchOrderBean = new OrderBean();
					searchOrderBean.setId(order_id);
					
					List<OrderBean> orders = ob.search(searchOrderBean);
					if(!orders.isEmpty())
						pb.setOrder(orders.get(0));
					
					search.add(pb);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}

	@Override
	public boolean persist (PaymentBean pb) throws ErrorBean {
		pb.isValid();
		
		int payment_id = pb.getId();
		
		OrderBean order = pb.getOrder();
		int order_id = -1;
		if (order != null)
			order_id = order.getId();
		
		String payment_method = pb.getPaymentMethod();
		Double amount = pb.getAmount();
		
		Calendar payDate = pb.getDatePaid();
		Timestamp date = new Timestamp(payDate.getTimeInMillis());
		
		if (payment_id == 0) {
			try {
				CallableStatement insertSQL = getInsertSQL();
				insertSQL.setString(1, payment_method);
				
				if (order_id > 0)
					insertSQL.setInt(2, order_id);
				else 
					insertSQL.setNull(2, Types.INTEGER);
				
				insertSQL.setTimestamp(3, date);
				insertSQL.setDouble(4, amount);

				insertSQL.execute();
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_INSERT_ERROR,
					e.getMessage(),
					this);
			}
		} else if (payment_id > 0) {
			try {
				CallableStatement updateSQL = getUpdateSQL();
				updateSQL.setInt(1, payment_id);
				updateSQL.setString(2, payment_method);
				
				if (order_id > 0)
					updateSQL.setInt(3, order_id);
				else 
					updateSQL.setNull(3, Types.INTEGER);
				
				updateSQL.setTimestamp(4, date);
				updateSQL.setDouble(5, amount);

				updateSQL.execute();
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_UPDATE_ERROR,
					e.getMessage(),
					this);
			}
		}
		return false;
	}

	@Override
	public boolean remove (PaymentBean pb) throws ErrorBean {
		int payment_id = pb.getId();
		
		try {
			CallableStatement deletePaymentSQL = getDeletePaymentSQL();
			deletePaymentSQL.setInt(1, payment_id);
			deletePaymentSQL.execute();
			return true;
		} catch(SQLException e) {
			throwError(
				BROKER_DELETE_ERROR,
				e.getMessage(),
				this);
			return false;
		}
	}

	@Override
	public List<PaymentBean> search (PaymentBean searchBean) throws ErrorBean {
		searchBean.isValid();
		
		List<PaymentBean> search = new ArrayList<PaymentBean>();
		PaymentBean pb;
		
		boolean isFirst = true;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM payment");
		
		//Search by order id
		OrderBean order = searchBean.getOrder();
		int order_id = order.getId();
		if (order_id > 0) {
			sql.append(appendClause("fk_order_id = " + order_id, isFirst));
			isFirst = false;
		}
		
		//Search by payment id
		int payment_id = searchBean.getId();
		if (payment_id > 0) {
			sql.append(appendClause("payment_id = " + payment_id, isFirst));
			isFirst = false;
		}
		
		//Search by payment method
		String payment_method = searchBean.getPaymentMethod();
		if (!"".equals(payment_method) && payment_method != null) {
			sql.append(appendClause("payment_method LIKE '%" + payment_method + "%'", isFirst));
			isFirst = false;
		}
		
		//Search by amount
		double amount = searchBean.getAmount();
		if (amount > 0.0) {
			sql.append(appendClause("amount = " + amount, isFirst));
			isFirst = false;
		}

		//Search by date
		Calendar payDate = searchBean.getDatePaid();
		if (payDate != null) {
			Date date = new Date(payDate.getTimeInMillis());
			sql.append(appendClause(" AND DATE(reservation_date) = '" + date.toString() + "'", isFirst));
			isFirst = false;
		}
		
		sql.append(";");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					pb = new PaymentBean();
					pb.setId(results.getInt("payment_id"));
					pb.setPaymentMethod(results.getString("payment_method"));
					pb.setAmount(results.getDouble("amount"));
					
					//Retrieve the order
					order_id = results.getInt("fk_order_id");
					OrderBroker ob = OrderBroker.getBroker();
					OrderBean searchOrderBean = new OrderBean();
					searchOrderBean.setId(order_id);
					
					List<OrderBean> orders = ob.search(searchOrderBean);
					if(!orders.isEmpty())
						pb.setOrder(orders.get(0));
					
					search.add(pb);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	/* END (Broker) Methods */
	
}
